package project.weather.dao;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import project.weather.dao.WeatherData;
import project.weather.dao.DbRemote;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class WeatherDocumentDao {
    public void showDebug(String msg){
        System.out.println("["+(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(new Date())+"][documents/dao/Db]"+msg);
    }

            /*添加记录*/
    public void addWeatherInfRecord(WeatherData data, JSONObject json) throws JSONException, SQLException {
        // 增添天气时后台主动添加时间
        String AddTime=(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(new Date());
        //构造sql语句，根据传递过来的条件参数
        String location=data.getParam().has("location")?data.getParam().getString("location"):null;
        String date=data.getParam().has("date")?data.getParam().getString("date"):null;
        String type=data.getParam().has("type")?data.getParam().getString("type"):null;
        String fengxiang=data.getParam().has("fengxiang")?data.getParam().getString("fengxiang"):null;
        String Maxtemperature=data.getParam().has("Maxtemperature")?data.getParam().getString("Maxtemperature"):null;
        String Mintemperature=data.getParam().has("Mintemperature")?data.getParam().getString("Mintemperature"):null;
        String fengli=data.getParam().has("fengli")?data.getParam().getString("fengli"):null;

        if(location!=null){
            String sql="insert into xm05_weather_information(location,type,max_temperature,min_temperature,fengli,fengxiang,create_time,date)";
            sql=sql+" values('"+location+"'";
            sql=sql+",'"+type+"'";
            sql=sql+",'"+Maxtemperature+"'";
            sql=sql+",'"+Mintemperature+"'";
            sql=sql+",'"+fengli+"'";
            sql=sql+",'"+fengxiang+"'";
            sql=sql+",'"+AddTime+"'";
            sql=sql+" ,'"+date+"')";
            data.getParam().put("sql",sql);
            updateRecord(data,json);

            showDebug("[addWeatherInfRecord]构造的sql语句="+sql);
        }
    }
    /*删除记录*/
    public void deleteWeatherRecord(WeatherData data, JSONObject json) throws JSONException, SQLException{
        //构造sql语句，根据传递过来的条件参数
        String id=data.getParam().has("id")?data.getParam().getString("id"):null;
        if(id!=null){
            String sql="delete from xm05_weather_information where id="+data.getParam().getString("id");
            data.getParam().put("sql",sql);
            updateRecord(data,json);
        }
    }
    /*修改记录*/
    public void modifyWeatherRecord(WeatherData data, JSONObject json) throws JSONException, SQLException{
        // 修改文件需要把修改时间给换掉
        String modifyTime=(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(new Date());
       // 构造sql语句
        String id=data.getParam().has("id")?data.getParam().getString("id"):null;
        String Maxtemperature=data.getParam().has("max_temperature")?data.getParam().getString("max_temperature"):null;
        String Mintemperature=data.getParam().has("min_temperature")?data.getParam().getString("min_temperature"):null;
        String WeatherType=data.getParam().has("type")?data.getParam().getString("type"):null;
        if(id!=null){
            String sql="update xm05_weather_information";
            sql=sql+" set max_temperature='"+Maxtemperature+"'";
            sql=sql+" ,min_temperature='"+Mintemperature+"'";
            sql=sql+" ,type='"+WeatherType+"'";
            sql=sql+" ,modify_time='"+modifyTime+"'";
            sql=sql+" where id="+id;
            data.getParam().put("sql",sql);
            updateRecord(data,json);
        }
    }
    /*查询记录*/
    public void getWeatherRecord(WeatherData data, JSONObject json) throws JSONException, SQLException{
        //构造sql语句，根据传递过来的查询条件参数
        String sql=createGetRecordSql(data);			//构造sql语句，根据传递过来的查询条件参数
        data.getParam().put("sql",sql);
        queryRecord(data,json);
    }

    /*查询指定记录*/
    public void queryWeatherInfRecord(WeatherData data, JSONObject json) throws JSONException, SQLException{
        //构造sql语句，根据传递过来的条件参数
        String content=data.getParam().has("content")?data.getParam().getString("content"):null;
        if(content!=null){
            String sql="select * from xm05_weather_information where "+data.getParam().getString("id");
            data.getParam().put("sql",sql);
            updateRecord(data,json);
        }
    }
    /*
     * 这是一个样板的函数，可以拷贝做修改用
     */
    private void updateRecord(WeatherData data, JSONObject json) throws JSONException, SQLException{
        /*--------------------获取变量 开始--------------------*/
        JSONObject param=data.getParam();
        int resultCode=0;
        String resultMsg="ok";
        /*--------------------获取变量 完毕--------------------*/
        /*--------------------数据操作 开始--------------------*/
        DbRemote updateDb = new DbRemote("yjykfsj2021");
        String sql=data.getParam().getString("sql");
        showDebug("[updateRecord]"+sql);
        updateDb.executeUpdate(sql);
        updateDb.close();
        /*--------------------数据操作 结束--------------------*/
        /*--------------------返回数据 开始--------------------*/
        json.put("result_msg",resultMsg);															//如果发生错误就设置成"error"等
        json.put("result_code",resultCode);														//返回0表示正常，不等于0就表示有错误产生，错误代码
        /*--------------------返回数据 结束--------------------*/
    }
    private void queryRecord(WeatherData data, JSONObject json) throws JSONException, SQLException{
        /*--------------------获取变量 开始--------------------*/
        String resultMsg = "ok";
        int resultCode = 0;
        List jsonList = new ArrayList();
        List jsonName = new ArrayList();
        /*--------------------获取变量 完毕--------------------*/
        /*--------------------数据操作 开始--------------------*/
        DbRemote queryDb = new DbRemote("yjykfsj2021");
        String sql=data.getParam().getString("sql");
        showDebug("[queryRecord]构造的SQL语句是：" + sql);
        try {
            ResultSet rs = queryDb.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int fieldCount = rsmd.getColumnCount();
            while (rs.next()) {
                Map map = new HashMap();
                for (int i = 0; i < fieldCount; i++) {
                    map.put(rsmd.getColumnName(i + 1), rs.getString(rsmd.getColumnName(i + 1)));
                }
                jsonList.add(map);
            }
            rs.close();
            // 为了返回excel，需要添加表头信息
            for(int i=0;i<fieldCount;i++)
            {
                String columnlabel = rsmd.getColumnLabel(i+1);
                jsonName.add(columnlabel);
            }
        } catch (Exception e) {
            e.printStackTrace();
            showDebug("[queryRecord]查询数据库出现错误：" + sql);
            resultCode = 10;
            resultMsg = "查询数据库出现错误！" + e.getMessage();
        }
        queryDb.close();
        /*--------------------数据操作 结束--------------------*/
        /*--------------------返回数据 开始--------------------*/
        json.put("aaData",jsonList);
        json.put("aaFileName",jsonName);
        json.put("result_msg",resultMsg);															//如果发生错误就设置成"error"等
        json.put("result_code",resultCode);														//返回0表示正常，不等于0就表示有错误产生，错误代码
        /*--------------------返回数据 结束--------------------*/
    }

    private String createGetRecordSql(WeatherData data) throws JSONException {
        String sql="select * from xm05_weather_information";
        String id=data.getParam().has("id")?data.getParam().getString("id"):null;

        if(id!=null)
            sql=sql+" where id="+id;
        return sql;
    }


    public void getWeatherInfBySomeType(WeatherData data, JSONObject json) throws JSONException, SQLException{
        /*--------------------获取变量 开始--------------------*/
        //排序要求
        String resultMsg = "ok";
        int resultCode = 0;
        List jsonList = new ArrayList();
        String timeFrom=(new SimpleDateFormat("yyyy-MM-dd 00:00:00")).format(new Date());
        String timeTo=(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(new Date());

        /*--------------------获取变量 完毕--------------------*/
        /*--------------------数据操作 开始--------------------*/
        //排序要求
        String orderBy=data.getParam().has("order_by")?data.getParam().getString("order_by"):null;
        String groupBy=data.getParam().has("group_by")?data.getParam().getString("group_by"):null;

        DbRemote db = new DbRemote("yjykfsj2021");
        String sql="select type,count(*) as number from xm05_weather_information";
        if(groupBy!=null){
            sql = sql+" group by "+groupBy;
        }
        if(orderBy!=null){
            sql = sql+" order by "+orderBy;
        }
        showDebug("[getWeatherInfBySomeType]构造的SQL语句是：" + sql);
        try {
            ResultSet rs = db.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int fieldCount = rsmd.getColumnCount();
            while (rs.next()) {
               HashMap map = new HashMap();
               map.put("type",rs.getString("type"));
               map.put("number",rs.getInt("number"));
               jsonList.add(map);
            }
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
            showDebug("[getWeatherInfBySomeType]查询数据库出现错误：" + sql);
            resultCode = 10;
            resultMsg = "查询数据库出现错误！" + e.getMessage();
        }
        db.close();
        /*--------------------数据操作 结束--------------------*/
        /*--------------------返回数据 开始--------------------*/
        json.put("aaData",jsonList);
        json.put("result_msg",resultMsg);															//如果发生错误就设置成"error"等
        json.put("result_code",resultCode);														//返回0表示正常，不等于0就表示有错误产生，错误代码
        /*--------------------返回数据 结束--------------------*/
    }


}

